/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package geraphp;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author alyson
 */
public class ConexaoBD {

    private Connection con = null;

    public boolean estaConectado() throws SQLException {
        boolean estaConectado = false;

        try {
            estaConectado = !con.isClosed();
        } catch (Exception ex) {
            estaConectado = false;
        }
        return estaConectado;
    }

    public void conectarBD(String host, String login, String senha, String banco) {
        try {
            Class.forName("org.postgresql.Driver").getInterfaces();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConexaoBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            con = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + banco, login, senha);
        } catch (SQLException ex) {
            Logger.getLogger(ConexaoBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void desconectarBD() {
        try {
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConexaoBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Object[] obterEsquemas() throws SQLException {

        DatabaseMetaData dbmd = null;
        dbmd = con.getMetaData();

        String[] types = {
            "TABLE"
        };

        ResultSet resultSet = null;
        resultSet = dbmd.getTables(null, null, "%", types);

        resultSet.beforeFirst();

        String schemaName = "";
        ArrayList esquemas = new ArrayList();

        while (resultSet.next()) {
            if (!schemaName.equalsIgnoreCase(resultSet.getString(2))) {
                //schemas
                schemaName = resultSet.getString(2);
                esquemas.add(schemaName);
            }
        }

        return esquemas.toArray();
    }

    public String[] obterTabelas(String esquema) {
        ArrayList<String> tabelas = new ArrayList<String>();

        try {
            DatabaseMetaData dbmd = null;
            dbmd = con.getMetaData();

            String[] types = {
                "TABLE"
            };

            ResultSet resultSet = null;
            resultSet = dbmd.getTables(null, null, "%", types);

            resultSet.last();

            resultSet.beforeFirst();


            while (resultSet.next()) {
                //tabelas
                if (resultSet.getString(2).equalsIgnoreCase(esquema)) {
                    tabelas.add(resultSet.getString(3));
                }
            }

        } catch (SQLException e) {
        }

        return (String[]) (tabelas.toArray(new String[tabelas.size()]));
    }

    public ArrayList obterCampos(String tabela) {
        ArrayList campos = new ArrayList();
        ArrayList subCampo = new ArrayList();

        DatabaseMetaData dbmd = null;

        try {
            dbmd = con.getMetaData();
            ResultSet resultsetCol = dbmd.getColumns(null, null, tabela, null);
            resultsetCol.beforeFirst();

            ResultSet resultsetColPk = dbmd.getPrimaryKeys(null, null, tabela);

            String pkColuna = "";
            while (resultsetColPk.next()) {
                pkColuna = resultsetColPk.getString("COLUMN_NAME");
            }

            while (resultsetCol.next()) {
                subCampo = new ArrayList();

                if (resultsetCol.getString("COLUMN_NAME").equalsIgnoreCase(pkColuna)) {
                    subCampo.add("PK");
                } else {
                    subCampo.add("");
                }
                subCampo.add(resultsetCol.getString("COLUMN_NAME"));
                subCampo.add(resultsetCol.getString("TYPE_NAME"));
                subCampo.add(resultsetCol.getString("column_size"));
                subCampo.add(resultsetCol.getString("is_nullable"));

                campos.add(subCampo);
                subCampo = null;
            }

        } catch (SQLException e) {
        }

        return campos;
    }
}
